2 2
Hi everybody! In this video, we will learn how to create a bridge chart. 3 3
Bridge charts, often called waterfall charts, are very popular graphic tools that are often used in corporate presentations. 4 4
A bridge normally shows how an initial value is affected by a series of intermediate positive or negative values. 5 5
Bridge charts are not native to Microsoft Excel, which slightly complicates their creation. 6 6
Sometimes large firms have their own ad-ins, allowing the creation of bridge charts, but you 7 7
can always benefit from being able to build this chart from scratch. 8 8
The method, which we will see in this video is probably the simplest for the creation of bridge charts. In the sheet "Bridge chart", we have a 9 9
ready 10 10
ready made bridge chart, which will be the final output of our exercise. 11 11
Let's take a look at it. The chart shows us the 2012 Ebitda and then through intermediate 12 12
steps - "Change in revenues", "Change in variable costs" and "Change in opex", 13 13
it explains to us what influenced the different value for Ebitda in 2013. Revenues were higher by 0.3 million in 2013, variable costs were higher 14 14
in 2013 as well, which has a negative impact on Ebitda. 15 15
Operating expenses were lower, though as we can see had a positive effect of plus 1.0 million going from 2012 to 2013. 16 16
And we arrive to the 2013 value for Ebitda - 5.3 million. Now that we've seen what it is and how it functions, let's build our bridge chart from 17 17
18 18
scratch. First of all, we need are starting value, 19 19
the intermediate values, which drive the change and the final value. 20 20
In our case these will be the same as the chart, which is ready. 21 21
Ebitda for FY12, 22 22
"Change in revenues", "Change in variable costs", 23 23
"Change in opex" and Ebitda FY13. Ebitda for FY12 is something that we have. 24 24
We can just go to the P&L sheet and link them. 25 25
Ok. The change for each of the intermediate steps is something that we need to calculate for each of the steps we have to go to the P&L 26 26
27 27
sheet and take the difference between the value for 2013 28 28
and the value for 2012. 29 29
Please note, that the operation is done in the same way for revenues and costs. 30 30
I'll go and take the difference for each of the intermediate steps. 31 31
After we've carried out this operation for the three intermediate steps, the 32 32
data source for our bridge chart is ready. I'll link to the value of Ebitda for 2013 in order to facilitate the check. 33 33
It is always good to make a check to see whether the sum of the value, which we have after considering the initial value and all the 34 34
intermediate steps is the same as the final value. Our check shows that the numbers are Ok and we can go ahead. 35 35
Let's copy, and use "Paste Special" clicking "transpose" in order to obtain the names of the components of our chart in the following way. 36 36
Ebitda FY12, 37 37
"Change in revenues", "Change in variable costs", 38 38
"Change in opex" and Ebitda FY13. 39 39
Ok. We have the names of the components on a single row. We need to start from Ebitda FY12. 40 40
I'll select its value and link it to the data source above. 41 41
Now imagine that the basis for our chart is a stacked column chart. If we look carefully to the right, where we have the ready bridge chart, we 42 42
can notice that it actually is a Stacked Column chart with some transparent components, 43 43
or we need to create the Stacked Column chart in a way that would allow us to transform it into 44 44
a bridge afterwards. So will there be anything above the value of Ebitda FY12? 45 45
No. We can see that in the ready chart on the right. 46 46
So let's write zero below 4.2. Next, we have "Change in revenues". 47 47
Let's link the value for Ebitda FY12 48 48
in the first cell. 49 49
In the cell below, we'll answer the question how did the "change in revenues" influence Ebitda 50 50
for FY13? It had a positive affect of +0.3, so we'll link to such change 51 51
and thus show that the overall quantity increased to 4.5. 52 52
See how it is in the ready bridge chart? Think of these plus 0.3 million, 53 53
as a step which increments the initial value of 4.2 million. Things become a little trickier when the change is negative. So far, calculating 54 54
55 55
roughly, we started from 4.2 and added 0.3, which took us to 4.5. 56 56
Now, we will have a negative impact of 0.2. The value needs to decrease and therefore we need 57 57
to show how the 0.2 reduced our current amount of 4.5. 58 58
We will have one part of the column, which shows what the result is after the decrease and one part which represents the actual decrease. 59 59
60 60
The column has two components. The amount of negative change and the amount, which remains after the negative change. 61 61
We need to have these two components in the cells here. In the first cell, we will have the amount, which remains after the negative 62 62
63 63
change. It is given by the sum of the amount at which we had arrived 64 64
before the negative change in this step. 65 65
In the cell below, I'll take the negative change and reverse its sign. These two values would allow us to have what we see in the bridge chart 66 66
on the right. The transparent value is where we've arrived, while the negative change is over here. 67 67
The next component "Change in Opex" is positive. 68 68
So we can do the same thing, we did for "Change in revenues". We'll take the value at which we arrive in our previous step in the cell 69 69
70 70
below. We'll have the positive component of "Change in Opex" - 71 71
plus 1.0 million. For our ending point, we'll have only the value at which we arrived in our last step. That would be 4.3 million 72 72
plus 1.0 million. 73 73
Ok. Now let's select all the numbers and create a Column Stacked chart. 74 74
The first thing that we will do 75 75
in order to turn this into a bridge chart is to select the first series of components in the middle, and remove the fill of the data points. 76 76
This is done in the following way. 77 77
Select the data point and choose "no fill". 78 78
Be careful to pick only single columns and not the entire series. 79 79
Now, let's change the colors of positive and negative changes. 80 80
Generally speaking, it is a good practice to have all of the positive changes with the same color 81 81
and all of the negative changes with another color. 82 82
Here all picked green for the positive changes and bright red for the negative 83 83
I'll spend another second, in order to color our two endpoints in dark blue. 84 84
The default labels are in black, but probably the better option is to have them in white and in bold. 85 85
Now, let's do all of our familiar formatting, which we saw in the example before. Let's remove the fill and the border color of the chart. 86 86
Let's select it and through a right-click change its font to "Arial" 8. 87 87
This is better. Right? 88 88
And another thing is that I don't need these labels - "Series 1" and "Series 2". Let's erase them. 89 89
Let's give a Half page format to our chart, by modifying its dimensions in a way that it becomes similar to the example chart. 90 90
I'll need to reduce the height and enlarge its width. 91 91
Let's take care of the gridlines. Remember how it's done? We need to pick one of them, 92 92
right-click on it, select "Format Gridlines", 93 93
after which we choose a grey line color and adjust its transparency to 75%. 94 94
We're missing some titles for our bridge chart. Let's add them. 95 95
A right-click on the chart, "Select Data", and I'll be able to pick the horizontal axis titles of my choosing. 96 96
Let's add a title to our axis in order to clarify the currency 97 97
and the amounts that we're using. 98 98
We have to go to "Chart Tools", 99 99
select "Axis Titles", 100 100
and pick a title option for our vertical axis. 101 101
Ok. Let's type the unit of measurement within it- 102 102
we have Euro in million. Perfect. 103 103
the final thing we're missing is the chart title 104 104
Let's go again to the "Chart Tools" menu. 105 105
There we can select a chart title, which will be above our bridge chart. Let's use the same title as 106 106
the one of the chart, which is ready - "Ebitda bridge FY12 - FY13". 107 107
I'll apply the same formatting for titles that we saw for the other charts in this Case Study. Font "Arial", font size 9, 108 108
the text color is dark blue, 109 109
and the position of the title is on the left side of the chart. 110 110
this is how a bridge chart is created and formatted 111 111
I hope the video was useful 112 112
In case you have some doubts, 113 113
just try to visualize what needs to be done in order to obtain a bridge chart from a Column 114 114
Stacked chart and you'll get there. Moreover, if you have any questions, 115 115
you can just drop us a line and will go through them together. 116 116
Thanks for watching!